<%@page import="java.text.SimpleDateFormat"%>
<%@page import="java.util.Calendar"%>
<%@ page import="java.sql.*" %>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Employee Details</title>
<script type="text/javascript" src="scripts/jquery-1.5.1.min.js"></script>
<script type="text/javascript" src="scripts/jquery.meio.mask.js"></script>
<script type="text/javascript" >
  (function($){
    // call setMask function on the document.ready event
      $(function(){
        $('input:text').setMask();
      }
    );
  })(jQuery);
</script>
</head>
<body>
<%
	String actionName = request.getParameter("action");
	boolean errorFlag = false;
	boolean formFlag = true;
	String error = "", message = "";

	String empid = request.getParameter("empid");
	String fname = request.getParameter("fname");
	String lname = request.getParameter("lname");
	String doj = request.getParameter("doj");
	String dob = request.getParameter("dob");
	String dor = request.getParameter("dor");
	String grade = request.getParameter("grade");
	String desig = request.getParameter("desig");
	String dept = request.getParameter("dept");
	String email = request.getParameter("email");
	String phone = request.getParameter("phone");
	String ext = request.getParameter("ext");
	String lastdate = request.getParameter("lastdate");
	String status = request.getParameter("status");
%> 

<form name="form" action="menu.jsp?page=employee" method="post">
<input type="hidden" name="action" />
<input type="hidden" name="empid" value="<%=empid == null ? "" : empid%>" />
<%
	if(actionName==null || actionName.equals("")){
		
	}else{
	 	//Process form data
	 	Connection conn = null;
	 	try {
	 		Class.forName("oracle.jdbc.driver.OracleDriver");
	 		conn = DriverManager.getConnection(
	 				"jdbc:oracle:thin:@localhost:1521:XE", "prabz", "yellow");
	 		if(actionName.equals("create") || actionName.equals("update")){	
	 			if(fname==null || fname.equals("") || lname==null || lname.equals("")){
	 				errorFlag = true;
	 				error = "Name cannot be empty";
	 			}else{
			 		ResultSet rs;
			 		Integer employeeId = null;
			 		Date dojDt=null, dobDt=null, dorDt=null, lastDt=null; 
			 		Integer statusId=0;
			 		try{
			 			SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");		 			
			 			if(empid!=null && empid.length()>0)	employeeId = Integer.parseInt(empid);
			 			if(doj!=null && doj.length()>0) dojDt = new Date(sdf.parse(doj).getTime());
			 			if(dob!=null && dob.length()>0) dobDt = new Date(sdf.parse(dob).getTime());
			 			if(dor!=null && dor.length()>0) dorDt = new Date(sdf.parse(dor).getTime());
			 			if(lastdate!=null && lastdate.length()>0) lastDt = new Date(sdf.parse(lastdate).getTime());
			 			if(status!=null && status.length()>0) statusId = Integer.parseInt(status);
			 		}catch(Exception e){ e.printStackTrace();;}
			 		if(actionName.equals("create")){
			 			//INSERT INTO EMPLOYEES (EMP_ID,FNAME,LNAME,DOJ,DOB,DOR,GRADE,DESIGNATION,DEPARTMENT,EMAIL,PHONE,EXT,LASTDATE,STATUS) VALUES (0 /*not nullable*/,'s','s',{d '2011-03-30'} /*not nullable*/,{d '2011-03-30'} /*not nullable*/,{d '2011-03-30'} /*not nullable*/,'s','s','s','s','s','s',{d '2011-03-30'} /*not nullable*/,0);
			 			//CREATE SEQUENCE S_EMPLOYEE_ID; // IN ORACLE FOR THIS TO WORK
			 			StringBuilder insertSql = new StringBuilder("INSERT INTO EMPLOYEES (EMP_ID,FNAME,LNAME,DOJ,DOB,DOR,GRADE,DESIGNATION,DEPARTMENT,EMAIL,PHONE,EXT,LASTDATE,STATUS) ");
			 			insertSql.append("VALUES (S_EMPLOYEE_ID.NEXTVAL,?,?,?,?,?,?,?,?,?,?,?,?,?)");
			 			PreparedStatement stmt = conn.prepareStatement(insertSql.toString());
			 			stmt.setString(1, fname); stmt.setString(2, lname); stmt.setDate(3, dojDt); stmt.setDate(4, dobDt);stmt.setDate(5, dorDt);
			 			stmt.setString(6, grade); stmt.setString(7, desig); stmt.setString(8, dept); stmt.setString(9, email);
			 			stmt.setString(10, phone); stmt.setString(11, ext); stmt.setDate(12, lastDt); stmt.setInt(13, statusId);
			 			stmt.executeUpdate();
			 			message="Employee Record Updated Successfully!";
			 		}else{
			 			//UPDATE EMPLOYEES (EMP_ID,FNAME,LNAME,DOJ,DOB,DOR,GRADE,DESIGNATION,DEPARTMENT,EMAIL,PHONE,EXT,LASTDATE,STATUS) VALUES (0 /*not nullable*/,'s','s',{d '2011-03-30'} /*not nullable*/,{d '2011-03-30'} /*not nullable*/,{d '2011-03-30'} /*not nullable*/,'s','s','s','s','s','s',{d '2011-03-30'} /*not nullable*/,0);
			 			//CREATE SEQUENCE S_EMPLOYEE_ID; // IN ORACLE FOR THIS TO WORK
			 			StringBuilder insertSql = new StringBuilder("UPDATE EMPLOYEES SET FNAME=?,LNAME=?,DOJ=?,DOB=?,DOR=?,GRADE=?,DESIGNATION=?,DEPARTMENT=?,EMAIL=?,PHONE=?,EXT=?,LASTDATE=?,STATUS=? ");
			 			insertSql.append("WHERE EMP_ID="+empid);
			 			PreparedStatement stmt = conn.prepareStatement(insertSql.toString());
			 			stmt.setString(1, fname); stmt.setString(2, lname); stmt.setDate(3, dojDt); stmt.setDate(4, dobDt);stmt.setDate(5, dorDt);
			 			stmt.setString(6, grade); stmt.setString(7, desig); stmt.setString(8, dept); stmt.setString(9, email);
			 			stmt.setString(10, phone); stmt.setString(11, (ext==null?"":ext)); 
			 			stmt.setDate(12, lastDt); 
			 			stmt.setInt(13, statusId);
			 			stmt.executeUpdate();
			 			message="Employee Record Created Successfully!";
			 		}
	 			}
	 		}else if(actionName.equals("new") || actionName.equals("delete")){	
	 			//code for delete
	 			if(actionName.equals("delete")){
	 				Statement stmt = conn.createStatement();
		 			ResultSet rs = stmt.executeQuery("DELETE FROM EMPLOYEES WHERE EMP_ID="+empid);
		 			message="Employee Record Deleted Successfully!";	
	 			}
		 		empid = ""; fname = "";	lname = "";	doj = ""; dob = "";	dor = "";
		 		grade = "";	desig = "";	dept = "";	email = "";	phone = "";
		 		ext = ""; lastdate = ""; status = "";
	 		}else if(actionName.equals("select")){	
		 		Statement stmt = conn.createStatement();
		 		ResultSet rs = stmt.executeQuery("SELECT * FROM EMPLOYEES WHERE EMP_ID="+empid);
		 		SimpleDateFormat sdf = new SimpleDateFormat("dd/MM/yyyy");	
		 		while(rs.next()){
		 			empid = rs.getString("EMP_ID");
		 			fname = rs.getString("FNAME");
		 			lname =rs.getString("LNAME");
		 			java.sql.Date dojDt = rs.getDate("DOJ");
		 			if(dojDt!=null)doj= sdf.format(dojDt);
		 			java.sql.Date dobDt = rs.getDate("DOB");
		 			if(dobDt!=null)dob= sdf.format(dobDt);
		 			java.sql.Date dorDt = rs.getDate("DOR");
		 			if(dorDt!=null)dor= sdf.format(dorDt);
		 			grade = rs.getString("GRADE");
		 			desig = rs.getString("DESIGNATION");
		 			dept = rs.getString("DEPARTMENT");
		 			email = rs.getString("EMAIL");
		 			phone = rs.getString("PHONE");
		 			ext = rs.getString("EXT");
		 			java.sql.Date lastDt = rs.getDate("LASTDATE");
		 			if(lastDt!=null)lastdate= sdf.format(lastDt);
		 			status = rs.getString("STATUS");		 			
		 		}
	 		}else if(actionName.equals("browse")){	 			
		 		Statement stmt = conn.createStatement();
		 		ResultSet rs = stmt
		 				.executeQuery("SELECT * FROM EMPLOYEES ");
		 		boolean emptyFlag = true;
		 		formFlag = false;
		 		StringBuilder sb = new StringBuilder("");
		 		sb.append("<P ALIGN='left'>EMPLOYEE LIST</P>");
		 		sb.append("<TABLE STYLE='border-width:1px;border-style: solid;'><TR STYLE='border-width:1px;border-style: solid;'>");
	 			sb.append(" <TH>EMP ID</TH>");sb.append(" <TH>FNAME</TH>");sb.append(" <TH>LNAME</TH>");sb.append(" <TH>DOJ</TH>");
	 			sb.append(" <TH>DOB</TH>");sb.append(" <TH>DOR</TH>");sb.append(" <TH>GRADE</TH>");sb.append(" <TH>DESIGNATION</TH>");
	 			sb.append(" <TH>DEPARTMENT</TH>");sb.append(" <TH>EMAIL</TH>");sb.append(" <TH>PHONE</TH>");sb.append(" <TH>EXT</TH>");
	 			sb.append(" <TH>LAST DT</TH>");sb.append(" <TH>STATUS</TH>");sb.append("<TH>SELECT</TH>");sb.append("</TR>"); 	 		
		 		while (rs.next()) {
		 			emptyFlag = false;
		 			sb.append("<TR STYLE='border-width:1px;border-style: solid;'>");
		 			sb.append(" <TD>" + rs.getString("EMP_ID") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("FNAME") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("LNAME") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("DOJ") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("DOB") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("DOR") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("GRADE") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("DESIGNATION") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("DEPARTMENT") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("EMAIL") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("PHONE") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("EXT") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("LASTDATE") + "</TD>");
		 			sb.append(" <TD>" + rs.getString("STATUS") + "</TD>");
		 			sb.append(" <TD><input type=\"submit\" name=\"select\" value=\"Select\" onclick=\"document.forms[0].empid.value='"+rs.getString("EMP_ID")+"';document.forms[0].action.value='select';\"></TD>");
		 			sb.append("</TR>");
		 		}
		 		if(emptyFlag){
		 			sb.append("<TR><TD colspan=14><I>No Records Found</I></TD></TR>");
		 		}
		 		sb.append("</TABLE>");
		 		out.println(sb.toString());
	 		}	 		
	 	} catch (SQLException e) {
	 		out.println("SQLException: " + e.getMessage() + "<BR>");
	 		while ((e = e.getNextException()) != null)
	 			out.println(e.getMessage() + "<BR>");
	 		errorFlag = true; error = e.getMessage();
	 	} catch (ClassNotFoundException e) {
	 		out.println("ClassNotFoundException: " + e.getMessage()
	 				+ "<BR>");
	 		errorFlag = true; error = e.getMessage();
	 	} catch(Exception e){
	 		out.println("Exception: "+e.getMessage() +"<BR>");
	 		errorFlag = true; error = e.getMessage();
	 	}finally {
	 		//Clean up resources, close the connection.
	 		if (conn != null) {
	 			try {
	 				conn.close();
	 			} catch (Exception ignored) {
	 			}
	 		}
	 	}
	}
 %>
<% if(errorFlag){ %>
<p align="center" style="color: red; "><i><%=error %></i></p>
<% } %>
<% if(message!=null && message.length()>0){ %>
<p align="center" style="color: green; "><i><%=message %></i></p>
<% } %>
<% if(errorFlag || formFlag){ %>
	<table width="100%" border=0 style="background-color:ffeeff;border: 1">
	
	<tr><td colspan=2 align="center" style="font-weight:bold;font-size:20pt;" align="center">Employee Details</td>
	</tr>
	<tr><td colspan=2>&nbsp;</td>
	</tr>
	
	<tr><td style="font-size:12pt;" align="center">Select XL file location:</td>
	<td><input type="text" name="location" value=""></td>
	<td><input type="submit" name="browse" value="Browse" onclick="javascript:document.forms[0].action.value='browse';"/></td>
	</tr>
	
	<tr><td style="font-size:12pt;" align="center">Employee Id:</td>
	<td><%=(empid==null?"":empid)%></td>
	<td></td>
	</tr>
	
	<tr><td style="font-size:12pt;" align="center">Name:</td>
	<td><input type="text" name="fname" value="<%=(fname==null?"":fname)%>"><input type="text" name="lname" value="<%=(lname==null?"":lname)%>"></td>
	</tr>
	
	<tr><td style="font-size:12pt;" align="center">Gender:</td>
	<td><label><input type="radio" name="gender" value="male">Male</label><label><input type="radio" name="gender" value="female">Female</label></td>
	<td><input type="submit" name="new" value="New" onclick="javascript:document.forms[0].action.value='new';"></td>
	</tr>
	
	<tr><td style="font-size:12pt;" align="center">Blood Group:</td>
	<td><select name="blood">
	<option>O+ve</option>
	<option>O-ve</option>
	<option>A+ve</option>
	<option>A-ve</option>
	<option>B+ve</option>
	<option>B-ve</option>
	<option>AB+ve</option>
	<option>AB-ve</option>
	</select></td>
	<td><% if(empid==null || empid.length()==0){ %>
	<input type="submit" name="create" value="Create" onclick="javascript:document.forms[0].action.value='create';">
	<%} %></td>
	</tr>
	<tr><td style="font-size:12pt;" align="center">Date of Joining:</td>
	<td><input type="text" name="doj" alt='date-us' value="<%=(doj==null?"":doj)%>"></td>
	<td><% if(empid!=null && empid.length()>0){ %>
	<input type="submit" name="update" value="Update" onclick="javascript:document.forms[0].action.value='update';">
	<%} %></td>
	</tr>
	<tr><td style="font-size:12pt;" align="center">Date of Birth:</td>
	<td><input type="text" name="dob" alt='date-us'  value="<%=(dob==null?"":dob)%>"></td>
	<td></td>
	</tr>
	<tr><td style="font-size:12pt;" align="center">Date of Retirement:</td>
	<td><input type="text" name="dor" alt='date-us' value="<%=(dor==null?"":dor)%>"></td>
	<td><% if(empid!=null && empid.length()>0){ %>
	<input type="submit" name="delete" value="Delete" onclick="javascript:document.forms[0].action.value='delete';">
	<%} %></td>
	</tr>
	<tr><td style="font-size:12pt;" align="center">Grade:</td>
	<td><input type="text" name="grade" value="<%=grade == null ? "" : grade%>"></td>
	<td></td>
	</tr>
	<tr><td style="font-size:12pt;" align="center">Designation:</td>
	<td><input type="text" name="desig" value="<%=desig == null ? "" : desig%>"></td>
	<td><input type="submit" name="clear" value="Clear All" ></td>
	</tr>
	<tr><td style="font-size:12pt;" align="center">Department:</td>
	<td><input type="text" name="dept" value="<%=dept == null ? "" : dept%>"></td>
	<td><input type="submit" name="cancel" value="Cancel" onclick="return checkForm()";></td>
	</tr>
	<tr><td style="font-size:12pt;" align="center">Email:</td>
	<td><input type="text" name="email" value="<%=email == null ? "" : email%>"></td>
	<td><input type="submit" name="close" value="Close" onclick="return captureEvents(close())"></td>
	</tr>
	<tr><td style="font-size:12pt;" align="center">Contact Number:</td>
	<td><input type="text" name="phone" value="<%=phone == null ? "" : phone%>"></td>
	</tr>
	<tr><td style="font-size:12pt;" align="center">Last Date:</td>
	<td><input type="text" name="lastdate" alt='date-us' value="<%=(lastdate==null?"":lastdate)%>"></td>
	</tr>
	<tr><td style="font-size:12pt;" align="center">Status:</td>
	<td><input type="text" name="status" value="<%=status == null ? "" : status%>"></td>
	</tr>
	<tr><td colspan="2"></td></tr>
	</table>
<%} %>
</form>
</body>
</html>